import requests
from conf.sonar_config import *
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from tqdm import tqdm

# SonarQube server 配置
SONARQUBE_URL = sonarqube_url
USERNAME = sonarqube_username  # Replace with your username
PASSWORD = sonarqube_password  # Replace with your password

# 项目和业务域映射
project_dict = project_business_domain


# 查询所有项目
def get_all_projects(sonarqube_url, auth):
    all_projects = []
    page = 1
    page_size = 100
    while True:
        endpoint = f"{sonarqube_url}/api/projects/search"
        params = {
            'p': page,
            'ps': page_size
        }
        response = requests.get(endpoint, params=params, auth=auth)
        if response.status_code == 200:
            data = response.json()
            all_projects.extend(data['components'])
            if len(data['components']) < page_size:
                break
            page += 1
        else:
            print(f"Failed to retrieve projects. Status code: {response.status_code}")
            print(f"Response: {response.text}")
            break
    return all_projects


# Function to get security hotspots count for a project
def get_security_hotspots_count(sonarqube_url, project_key, auth):
    endpoint = f"{sonarqube_url}/api/hotspots/search"
    params = {
        'projectKey': project_key,
        'ps': '1'  # Page size (1 in this case, as we just want the total count)
    }
    response = requests.get(endpoint, params=params, auth=auth)
    if response.status_code == 200:
        data = response.json()
        return data['paging']['total']
    else:
        print(f"Failed to retrieve security hotspots for {project_key}. Status code: {response.status_code}")
        print(f"Response: {response.text}")
        return 0


# Function to get blocker issues count for a project
def get_blocker_issues_count(sonarqube_url, project_key, auth):
    endpoint = f"{sonarqube_url}/api/issues/search"
    params = {
        'componentKeys': project_key,
        'severities': 'BLOCKER',
        'statuses': 'OPEN,REOPENED,CONFIRMED',
        'ps': '1'  # Page size (1 in this case, as we just want the total count)
    }
    response = requests.get(endpoint, params=params, auth=auth)
    if response.status_code == 200:
        data = response.json()
        return data['paging']['total']
    else:
        print(f"Failed to retrieve blocker issues for {project_key}. Status code: {response.status_code}")
        print(f"Response: {response.text}")
        return 0


# Function to get the last analysis date for a project
def get_last_analysis_date(sonarqube_url, project_key, auth):
    endpoint = f"{sonarqube_url}/api/project_analyses/search"
    params = {
        'project': project_key,
        'ps': '1'  # Page size (1 in this case, as we just want the latest analysis)
    }
    response = requests.get(endpoint, params=params, auth=auth)
    if response.status_code == 200:
        data = response.json()
        analyses = data.get('analyses', [])
        if analyses:
            return analyses[0]['date']
        else:
            return 'N/A'
    else:
        print(f"Failed to retrieve last analysis date for {project_key}. Status code: {response.status_code}")
        print(f"Response: {response.text}")
        return 'N/A'


# Authentication
auth = (USERNAME, PASSWORD)

# Get all projects
projects = get_all_projects(SONARQUBE_URL, auth)

# Initialize statistics
domain_stats = {}
project_stats = {}

# Process each project
for project in tqdm(projects, desc="项目分析"):
    project_key = project['key']
    project_name = project['name']
    business_domain = project_dict.get(project_key, '未知')

    # Get security hotspots, blocker issues count, and last analysis date
    security_hotspot_count = get_security_hotspots_count(SONARQUBE_URL, project_key, auth)
    blocker_issues_count = get_blocker_issues_count(SONARQUBE_URL, project_key, auth)
    last_analysis_date = get_last_analysis_date(SONARQUBE_URL, project_key, auth)

    if business_domain not in domain_stats:
        domain_stats[business_domain] = {
            'security_hotspots': 0,
            'blocker_issues': 0
        }

    domain_stats[business_domain]['security_hotspots'] += security_hotspot_count
    domain_stats[business_domain]['blocker_issues'] += blocker_issues_count

    # Store individual project stats
    project_stats[project_name] = {
        'business_domain': business_domain,
        'security_hotspots': security_hotspot_count,
        'blocker_issues': blocker_issues_count,
        'last_analysis_date': last_analysis_date
    }

# Create an Excel workbook and worksheet
wb = Workbook()
ws1 = wb.active
ws1.title = "SonarQube Metrics by Domain"

# Write header for domain statistics
header = ["Business Domain", "Security Hotspots", "Blocker Issues"]
ws1.append(header)

# Write domain statistics data
for domain, stats in tqdm(domain_stats.items(), desc="业务域统计"):
    ws1.append([domain, stats['security_hotspots'], stats['blocker_issues']])

# Create a bar chart for security hotspots by domain
chart1 = BarChart()
chart1.title = "Security Hotspots by Business Domain"
data = Reference(ws1, min_col=2, min_row=1, max_row=len(domain_stats) + 1)
categories = Reference(ws1, min_col=1, min_row=2, max_row=len(domain_stats) + 1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(categories)
ws1.add_chart(chart1, "E5")

# Create a bar chart for blocker issues by domain
chart2 = BarChart()
chart2.title = "Blocker Issues by Business Domain"
data = Reference(ws1, min_col=3, min_row=1, max_row=len(domain_stats) + 1)
categories = Reference(ws1, min_col=1, min_row=2, max_row=len(domain_stats) + 1)
chart2.add_data(data, titles_from_data=True)
chart2.set_categories(categories)
ws1.add_chart(chart2, "E20")

# Create another worksheet for project statistics
ws2 = wb.create_sheet(title="SonarQube Metrics by Project")

# Write header for project statistics
header = ["Project Name", "Business Domain", "Security Hotspots", "Blocker Issues", "Last Analysis Date"]
ws2.append(header)

# Write project statistics data
for project_name, stats in tqdm(project_stats.items(), desc="项目统计"):
    ws2.append([project_name, stats['business_domain'], stats['security_hotspots'], stats['blocker_issues'],
                stats['last_analysis_date']])

# Create a bar chart for security hotspots by project
chart3 = BarChart()
chart3.title = "Security Hotspots by Project"
data = Reference(ws2, min_col=3, min_row=1, max_row=len(project_stats) + 1)
categories = Reference(ws2, min_col=1, min_row=2, max_row=len(project_stats) + 1)
chart3.add_data(data, titles_from_data=True)
chart3.set_categories(categories)
ws2.add_chart(chart3, "F5")

# Create a bar chart for blocker issues by project
chart4 = BarChart()
chart4.title = "Blocker Issues by Project"
data = Reference(ws2, min_col=4, min_row=1, max_row=len(project_stats) + 1)
categories = Reference(ws2, min_col=1, min_row=2, max_row=len(project_stats) + 1)
chart4.add_data(data, titles_from_data=True)
chart4.set_categories(categories)
ws2.add_chart(chart4, "F20")

# Save the workbook
wb.save("sonarqube_metrics.xlsx")

print("Data has been written to 'sonarqube_metrics.xlsx'")

